package ijournalimporter.util;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 *
 * @author eric
 */
public class ExcelUtil {

    public static Sheet readExcel(File sourceFile, String sheetname) {
        try {
            Workbook workbook = Workbook.getWorkbook(sourceFile);
            Sheet sheet = null;
            if (sheetname != null) {
                sheet = workbook.getSheet(sheetname);
            }else{
                sheet = workbook.getSheet(0);
            }
            
            return sheet;
        } catch (IOException ex) {
            Logger.getLogger(Util.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (BiffException ex) {
            Logger.getLogger(Util.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public static Sheet readExcel(String sourceFile, String sheetname) {
        return readExcel(new File(sourceFile), sheetname);
    }

    public static ArrayList<ArrayList> readExcelToArray(File sourceFile, String sheetname) {
        Sheet sheet = readExcel(sourceFile, sheetname);
        ArrayList<ArrayList> result = new ArrayList();

        // read Excel and check id
        Cell[] data = null;
        int rowNum = 1;
        while (true) {
            try {
                ArrayList<String> row = new ArrayList();
                data = sheet.getRow(rowNum);
                for (int i = 0; i < data.length; i++) {
                    String value = data[i].getContents();
                    if (Util.isNotBlank(value)) {
                        value = value.trim();
                    }
                    row.add(value);
                }
                result.add(row);
            } catch (Exception e) {
                break;
            }
            rowNum++;
        }
        return result;
    }

    public static ArrayList<ArrayList> readExcelToArray(String sourceFile, String sheetname) {
        return readExcelToArray(new File(sourceFile), sheetname);
    }

    public static boolean writeExcel(OutputStream os, String sheetname, ArrayList data) {
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            WritableSheet sheet = workbook.createSheet(sheetname, 0);
            for (int i = 0; i < data.size(); i++) {
                ArrayList row = (ArrayList) data.get(i);
                for (int j = 0; j < row.size(); j++) {
                    Label label = new Label(j, i, (String) row.get(j));
                    sheet.addCell(label);
                }
            }
            workbook.write();
            workbook.close();
            return true;
        } catch (WriteException ex) {
            Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } catch (IOException ex) {
            Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public static boolean writeExcel(File sourceFile, String sheetname, ArrayList data) {
        return writeExcel(Util.getBufferedOutputStream(sourceFile), sheetname, data);
    }

    public static boolean writeExcel(String sourceFile, String sheetname, ArrayList data) {
        return writeExcel(new File(sourceFile), sheetname, data);
    }
}
